﻿if object_id('v_Utility_Asset_XmissionStructuresWithPoles') is not null
	drop view v_Utility_Asset_XmissionStructuresWithPoles

GO

create view v_Utility_Asset_XmissionStructuresWithPoles as

select

	 structures.Utility_Asset_XMissionStructureId as StructureId
	,structures.Latitude as StructureLatitude
	,structures.Longitude as StructureLongitude
	,structures.Altitude as StructureAltitude
	,structures.HaveImages as StructureHasImages

	,poles.Utility_Asset_XMissionStructures_PoleId as PoleId
	,Row_Number() over(partition by structures.Utility_Asset_XMissionStructureId order by poles.Utility_Asset_XMissionStructures_PoleId) as PoleRank
	,IsNull(maps.Name, '{blank}') as PoleMap
	,IsNull(sections.Name, '{blank}') as PoleSection
	,IsNull(tops.Name, '{blank}') as [PoleTop]
	,IsNull(species.Name, '{blank}') as [PoleSpecies]
	,IsNull(classes.Name, '{blank}') as [PoleClass]

	,IsNull(convert(varchar, poles.Year), '{blank}') as PoleYear
	,poles.Latitude as PoleLatitide
	,poles.Longitude as PoleLongitude
	,poles.Altitude as PoleAltitude
	,IsNull(convert(varchar, poles.SatelliteCount), '{blank}') as PoleSatelliteCount
	,IsNull(convert(varchar, poles.SatelliteTime, 101), '{blank}') as PoleSatelliteTime
	,poles.SolutionAvailable as PoleSolutionAvailable
	,IsNull(convert(varchar, poles.AID), '{blank}') as PoleAID
	,IsNull(convert(varchar, poles.LVisit), '{blank}') as PoleLVisit
	,IsNull(convert(varchar, poles.Number), '{blank}') as PoleNumber
	,IsNull(convert(varchar, poles.Length), '{blank}') as PoleLength
	,IsNull(convert(varchar, poles.LVisitor), '{blank}') as PoleLVisitor

from

	-- structure
	tb_Utility_Asset_XMissionStructures structures

	-- poles
	join tb_Utility_Asset_XMissionStructures_Poles poles
		on poles.Utility_Asset_XMissionStructureFK = structures.Utility_Asset_XMissionStructureId 

	-- map
	left join tb_Utility_Polygons maps
		on poles.MapFk = maps.Utility_PolygonId

	-- section
	left join tb_Utility_Polygons sections
		on poles.SectionFk = sections.Utility_PolygonId

	-- tops
	left join tb_XMissionStructures_TopTypes tops
		on poles.XMissionStructures_PoleTopTypeFk = tops.XMissionStructures_TopTypeId

	-- species
	left join tb_XMissionStructures_PoleSpecies species
		on poles.XMissionStructures_PoleSpeciesFk = species.XMissionStructures_PoleSpeciesId

	-- classes
	left join tb_XMissionStructures_PoleClasses classes
		on poles.XMissionStructures_PoleClassFk = classes.XMissionStructures_PoleClassId

GO

select top 10 * from v_Utility_Asset_XmissionStructuresWithPoles